/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package karaoke.dao;

import karaoke.excepcion.DAOExcepcion;
import karaoke.modelo.Comprobante;
import karaoke.util.ConexionBD;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Alonso
 */
public class ComprobanteDAO extends BaseDAO {
    
    public List<Comprobante> buscarPorCodigo(String codigo) throws DAOExcepcion {
        String query = "SELECT A.*, B.CODIGO AS 'COD_RESERVA', CONCAT(C.NOMBRE,' ',C.APELLIDO_PATERNO) AS 'NOMBRE_CLIENTE' " +
                       "FROM COMPROBANTE A, RESERVA B, CLIENTE C " +
                       "WHERE A.FK_ID_RESERVA = B.ID_RESERVA AND B.FK_ID_CLIENTE = C.ID_CLIENTE AND A.CODIGO LIKE ?";
        List<Comprobante> lista = new ArrayList<Comprobante>();
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = ConexionBD.obtenerConexion();
            stmt = con.prepareStatement(query);
            stmt.setString(1, "%" + codigo + "%");
            rs = stmt.executeQuery();

            SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
            
            while (rs.next()) {
                Comprobante comprobante = new Comprobante();
                comprobante.setIdComprobante(rs.getInt("ID_COMPROBANTE"));
                comprobante.setCodigo(rs.getString("CODIGO"));
                comprobante.setFecha(rs.getDate("FECHA"));
                comprobante.setFechaFormato(sdf.format(comprobante.getFecha()));
                comprobante.setImporte(rs.getFloat("IMPORTE"));
                comprobante.setIgv(rs.getFloat("IGV"));
                comprobante.setTotal(rs.getFloat("TOTAL"));
                comprobante.setRuc(rs.getString("RUC") != null ? rs.getString("RUC") : "");
                comprobante.setIdReserva(rs.getInt("FK_ID_RESERVA"));
                comprobante.setCodReserva(rs.getString("COD_RESERVA"));
                comprobante.setNombreCliente(rs.getString("NOMBRE_CLIENTE"));
                lista.add(comprobante);
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            throw new DAOExcepcion(e.getMessage());
        } finally {
            this.cerrarResultSet(rs);
            this.cerrarStatement(stmt);
            this.cerrarConexion(con);
        }
        System.out.println(lista.size());
        return lista;
    }

    public Comprobante insertar(Comprobante comprobante) throws DAOExcepcion {
        String query = "INSERT INTO COMPROBANTE(CODIGO,FECHA,IMPORTE,IGV,TOTAL,RUC,FK_ID_RESERVA) values (?,?,?,?,?,?,?)";
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = ConexionBD.obtenerConexion();
            stmt = con.prepareStatement(query);
            stmt.setString(1, comprobante.getCodigo());
            stmt.setDate(2, new java.sql.Date(comprobante.getFecha().getTime()));
            stmt.setFloat(3, comprobante.getImporte());
            stmt.setFloat(4, comprobante.getIgv());
            stmt.setFloat(5, comprobante.getTotal());
            stmt.setString(6, comprobante.getRuc());
            stmt.setInt(7, comprobante.getIdReserva());

            int i = stmt.executeUpdate();
            if (i != 1) {
                throw new SQLException("No se pudo insertar");
            }
            // Obtener el ultimo id
            int id = 0;
            query = "SELECT LAST_INSERT_ID()";
            stmt = con.prepareStatement(query);

            rs = stmt.executeQuery();

            if (rs.next()) {
                id = rs.getInt(1);
            }
            comprobante.setIdComprobante(id);
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            throw new DAOExcepcion(e.getMessage());
        } finally {
            this.cerrarResultSet(rs);
            this.cerrarStatement(stmt);
            this.cerrarConexion(con);
        }
        return comprobante;
    }

    public Comprobante obtener(int idComprobante) throws DAOExcepcion {
        Comprobante comprobante = new Comprobante();
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            String query = "SELECT * FROM COMPROBANTE WHERE ID_COMPROBANTE=?";
            con = ConexionBD.obtenerConexion();
            stmt = con.prepareStatement(query);
            stmt.setInt(1, idComprobante);
            rs = stmt.executeQuery();
            
            if (rs.next()) {
                comprobante.setIdComprobante(rs.getInt("ID_COMPROBANTE"));
                comprobante.setCodigo(rs.getString("CODIGO"));
                comprobante.setFecha(rs.getDate("FECHA"));
                comprobante.setImporte(rs.getFloat("IMPORTE"));
                comprobante.setIgv(rs.getFloat("IGV"));
                comprobante.setTotal(rs.getFloat("TOTAL"));
                comprobante.setRuc(rs.getString("RUC"));
                comprobante.setIdReserva(rs.getInt("FK_ID_RESERVA"));
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            throw new DAOExcepcion(e.getMessage());
        } finally {
            this.cerrarResultSet(rs);
            this.cerrarStatement(stmt);
            this.cerrarConexion(con);
        }
        return comprobante;
    }

    public void eliminar(int idComprobante) throws DAOExcepcion {
        String query = "DELETE FROM COMPROBANTE WHERE ID_COMPROBANTE = ?";
        Connection con = null;
        PreparedStatement stmt = null;
        try {
            con = ConexionBD.obtenerConexion();
            stmt = con.prepareStatement(query);
            stmt.setInt(1, idComprobante);
            
            int i = stmt.executeUpdate();
            if (i != 1) {
                throw new SQLException("No se pudo eliminar");
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            throw new DAOExcepcion(e.getMessage());
        } finally {
            this.cerrarStatement(stmt);
            this.cerrarConexion(con);
        }
    }

    public Comprobante actualizar(Comprobante comprobante) throws DAOExcepcion {
        String query = "UPDATE COMPROBANTE SET FK_ID_RESERVA=? WHERE ID_COMPROBANTE=?";
        Connection con = null;
        PreparedStatement stmt = null;
        try {
            con = ConexionBD.obtenerConexion();
            stmt = con.prepareStatement(query);
         
            stmt.setInt(1, comprobante.getIdReserva());
            stmt.setInt(2, comprobante.getIdComprobante());
            
            int i = stmt.executeUpdate();
            if (i != 1) {
                throw new SQLException("No se pudo actualizar");
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            throw new DAOExcepcion(e.getMessage());
        } finally {
            this.cerrarStatement(stmt);
            this.cerrarConexion(con);
        }
        return comprobante;
    }

    public List<Comprobante> listar() throws DAOExcepcion {
        List<Comprobante> lista = new ArrayList<Comprobante>();
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = ConexionBD.obtenerConexion();
            String query = "SELECT A.*, B.CODIGO AS 'COD_RESERVA', CONCAT(C.NOMBRE,' ',C.APELLIDO_PATERNO) AS 'NOMBRE_CLIENTE' " +
                           "FROM COMPROBANTE A, RESERVA B, CLIENTE C " +
                           "WHERE A.FK_ID_RESERVA = B.ID_RESERVA AND B.FK_ID_CLIENTE = C.ID_CLIENTE";
            stmt = con.prepareStatement(query);
            rs = stmt.executeQuery();
            
            SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
            
            while (rs.next()) {
                Comprobante comprobante = new Comprobante();
                comprobante.setIdComprobante(rs.getInt("ID_COMPROBANTE"));
                comprobante.setCodigo(rs.getString("CODIGO"));
                comprobante.setFecha(rs.getDate("FECHA"));
                comprobante.setFechaFormato(sdf.format(comprobante.getFecha()));
                comprobante.setImporte(rs.getFloat("IMPORTE"));
                comprobante.setIgv(rs.getFloat("IGV"));
                comprobante.setTotal(rs.getFloat("TOTAL"));
                comprobante.setRuc(rs.getString("RUC") != null ? rs.getString("RUC") : "");
                comprobante.setIdReserva(rs.getInt("FK_ID_RESERVA"));
                comprobante.setCodReserva(rs.getString("COD_RESERVA"));
                comprobante.setNombreCliente(rs.getString("NOMBRE_CLIENTE"));
                lista.add(comprobante);
            }

        } catch (SQLException e) {
            System.err.println(e.getMessage());
            throw new DAOExcepcion(e.getMessage());
        } finally {
            this.cerrarResultSet(rs);
            this.cerrarStatement(stmt);
            this.cerrarConexion(con);
        }
        return lista;
    }
    
}
